﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;

namespace DBWorker
{
    public class DBWokerBase: IDBWorkerBase
    {
        #region Constructors
        public DBWokerBase()                                    
        {
        }
        public DBWokerBase(string server, string databaseName)  
        {
            Server = server;
            DatabaseName = databaseName;
        }
        public DBWokerBase(string path)                         
        {
            Server = path;
        }
        #endregion Constructors

        #region Properties

        public string Server                         
        {
            set { _server = value; }
            get { return _server; }
        }

        public string DatabaseName                   
        {
            set { _databaseName = value; }
            get { return _databaseName; }
        }
        public string UserID                         
        {
            set { _userID = value; }
            get { return _userID; }
        }
        public string Password                       
        {
            set { _password = value; }
            get { return _password; }
        }
        public string Port                           
        {
            set { _port = value; }
            get { return _port; }
        }

        private static string _server;
        private static string _databaseName;
        private static string _userID;
        private static string _password;
        private static string _port;


        public virtual string GetConnectionString()      
        { return null; }

        #endregion Properties

        #region Public Methods

        public  DataTable GetDataTableByCommand(IDbCommand command)     
        {
            return GetDataTableByCommand(command, -1);
        }

        public DataRow GetDataRowByCommand(IDbCommand command)          
        {
            return GetDataTableByCommand(command, 1).Rows[0];
        }

        public object GetObjectByCommand(IDbCommand command)            
        {
            IDataReader dataReader = null;

            try
            {
                command.Connection.Open();
                dataReader = command.ExecuteReader();

                dataReader.Read();
            }
            finally
            {
                if (dataReader != null)
                    dataReader.Close();

                if (command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
            return dataReader.GetValue(0);
        }

        public DataTable GetDataTableSchema(IDbCommand command)         
        {
            IDataReader dataReader = null;
            DataTable tableSchema = new DataTable();

            try
            {
                command.Connection.Open();
                dataReader = command.ExecuteReader();

                GetDataTableSchema(dataReader);
            }
            finally
            {
                if (dataReader != null)
                    dataReader.Close();

                if (command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
            return tableSchema;
        }

        public object ExecuteScalar(IDbCommand command)                   
        {
            object id = null;

            try
            {
                command.Connection.Close();
                command.Connection.Open();
                id = command.ExecuteScalar();
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();                    
                }
            }
            return id;
        }

        #endregion Protected Methods

        #region Private Methods

        private DataTable GetDataTableSchema(IDataReader dataReader)
        {
            DataTable schemaDataTable = new DataTable();
            DataTable resultDataTable = new DataTable();

            schemaDataTable = dataReader.GetSchemaTable();

            for (int i = 0; i < dataReader.FieldCount; i++)
            {
                resultDataTable.Columns.Add(schemaDataTable.Rows[i]["ColumnName"].ToString(),
                    ((System.Type)schemaDataTable.Rows[i]["DataType"]));
            }

            return resultDataTable;
        }

        private DataTable GetDataTableByCommand(IDbCommand command, int rowsCount)
        {
            DataTable resultDataTable;

            IDataReader dataReader = null;
            DataRow myNewRow;

            try
            {
                command.Connection.Open();
                dataReader = command.ExecuteReader();

                object[] row;
                row = new object[dataReader.FieldCount];

                resultDataTable = GetDataTableSchema(dataReader);

                int i = 0;
                while (dataReader.Read())
                {
                    if (i++ < rowsCount || rowsCount == -1)
                    {
                        dataReader.GetValues(row);
                        myNewRow = resultDataTable.Rows.Add(row);
                    }
                }
            }
            finally
            {
                if (dataReader != null)
                    dataReader.Close();

                if (command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
            return resultDataTable;
        }

        #endregion Private Methods
    }
}
